import Database from 'better-sqlite3';
import dbManager from './DatabaseManager';

export type ComparisonOperator = '=' | '<>' | '>' | '>=' | '<' | '<=' | 'LIKE' | 'IN' | 'NOT IN' | 'IS NULL' | 'IS NOT NULL';

export interface WhereCondition {
  field: string;
  operator: ComparisonOperator;
  value?: any;
}

export interface JoinCondition {
  table: string;
  type: 'INNER' | 'LEFT' | 'RIGHT' | 'FULL';
  on: string;
}

export interface OrderCondition {
  field: string;
  direction: 'ASC' | 'DESC';
}

export class QueryBuilder {
  private db: Database.Database;
  private tableName: string;
  private selectFields: string[] = ['*'];
  private whereConditions: WhereCondition[] = [];
  private joinConditions: JoinCondition[] = [];
  private groupByFields: string[] = [];
  private havingConditions: WhereCondition[] = [];
  private orderByConditions: OrderCondition[] = [];
  private limitValue?: number;
  private offsetValue?: number;
  private params: any[] = [];

  constructor(tableName: string) {
    this.db = dbManager.getDatabase();
    this.tableName = tableName;
  }

  /**
   * 设置要查询的字段
   */
  select(...fields: string[]): QueryBuilder {
    if (fields.length > 0) {
      this.selectFields = fields;
    }
    return this;
  }

  /**
   * 添加WHERE条件
   */
  where(field: string, operator: ComparisonOperator, value?: any): QueryBuilder {
    this.whereConditions.push({ field, operator, value });
    if (value !== undefined && !['IS NULL', 'IS NOT NULL'].includes(operator)) {
      this.params.push(value);
    }
    return this;
  }

  /**
   * 添加AND条件
   */
  and(field: string, operator: ComparisonOperator, value?: any): QueryBuilder {
    return this.where(field, operator, value);
  }

  /**
   * 添加OR条件
   */
  or(field: string, operator: ComparisonOperator, value?: any): QueryBuilder {
    // 为了实现OR，我们将其作为一个特殊的条件添加
    // 将上一个条件和当前条件用OR连接
    if (this.whereConditions.length > 0) {
      const lastCondition = this.whereConditions[this.whereConditions.length - 1];
      const orCondition = `${lastCondition.field} ${lastCondition.operator} ? OR ${field} ${operator} ?`;
      
      // 从条件列表中移除最后一个条件
      this.whereConditions.pop();
      
      // 添加新的OR条件
      this.whereConditions.push({
        field: orCondition,
        operator: '=',
        value: undefined
      });
      
      // 将值添加到参数列表
      if (value !== undefined && !['IS NULL', 'IS NOT NULL'].includes(operator)) {
        this.params.push(value);
      }
    } else {
      // 如果没有前置条件，则作为普通条件添加
      this.where(field, operator, value);
    }
    
    return this;
  }

  /**
   * 添加带括号的AND组条件
   */
  andWhere(callback: (qb: QueryBuilder) => void): QueryBuilder {
    const subQuery = new QueryBuilder(this.tableName);
    callback(subQuery);
    
    if (subQuery.whereConditions.length > 0) {
      const subParams = subQuery.params;
      this.params.push(...subParams);
      
      const subConditions = this.buildWhereConditionString(subQuery.whereConditions);
      this.whereConditions.push({
        field: `(${subConditions})`,
        operator: '=',
        value: undefined
      });
    }
    
    return this;
  }

  /**
   * 添加表连接
   */
  join(table: string, type: 'INNER' | 'LEFT' | 'RIGHT' | 'FULL', on: string): QueryBuilder {
    this.joinConditions.push({ table, type, on });
    return this;
  }

  /**
   * 添加内连接
   */
  innerJoin(table: string, on: string): QueryBuilder {
    return this.join(table, 'INNER', on);
  }

  /**
   * 添加左连接
   */
  leftJoin(table: string, on: string): QueryBuilder {
    return this.join(table, 'LEFT', on);
  }

  /**
   * 设置GROUP BY
   */
  groupBy(...fields: string[]): QueryBuilder {
    this.groupByFields.push(...fields);
    return this;
  }

  /**
   * 添加HAVING条件
   */
  having(field: string, operator: ComparisonOperator, value?: any): QueryBuilder {
    this.havingConditions.push({ field, operator, value });
    if (value !== undefined && !['IS NULL', 'IS NOT NULL'].includes(operator)) {
      this.params.push(value);
    }
    return this;
  }

  /**
   * 设置排序
   */
  orderBy(field: string, direction: 'ASC' | 'DESC' = 'ASC'): QueryBuilder {
    this.orderByConditions.push({ field, direction });
    return this;
  }

  /**
   * 设置查询结果数量限制
   */
  limit(limit: number): QueryBuilder {
    this.limitValue = limit;
    this.params.push(limit);
    return this;
  }

  /**
   * 设置查询结果偏移量
   */
  offset(offset: number): QueryBuilder {
    this.offsetValue = offset;
    this.params.push(offset);
    return this;
  }

  /**
   * 构建WHERE条件字符串
   */
  private buildWhereConditionString(conditions: WhereCondition[]): string {
    return conditions.map(condition => {
      if (['IS NULL', 'IS NOT NULL'].includes(condition.operator)) {
        return `${condition.field} ${condition.operator}`;
      } else if (['IN', 'NOT IN'].includes(condition.operator) && Array.isArray(condition.value)) {
        const placeholders = condition.value.map(() => '?').join(', ');
        return `${condition.field} ${condition.operator} (${placeholders})`;
      } else if (condition.field.startsWith('(') && condition.field.endsWith(')')) {
        // 对于子查询，直接返回字段名，不添加操作符和值
        return condition.field;
      } else {
        return `${condition.field} ${condition.operator} ?`;
      }
    }).join(' AND ');
  }

  /**
   * 构建SQL查询语句
   */
  build(): { sql: string; params: any[] } {
    let sql = `SELECT ${this.selectFields.join(', ')} FROM ${this.tableName}`;
    
    // 添加JOIN
    if (this.joinConditions.length > 0) {
      const joinStr = this.joinConditions.map(join => 
        `${join.type} JOIN ${join.table} ON ${join.on}`
      ).join(' ');
      sql += ` ${joinStr}`;
    }
    
    // 添加WHERE
    if (this.whereConditions.length > 0) {
      sql += ` WHERE ${this.buildWhereConditionString(this.whereConditions)}`;
    }
    
    // 添加GROUP BY
    if (this.groupByFields.length > 0) {
      sql += ` GROUP BY ${this.groupByFields.join(', ')}`;
    }
    
    // 添加HAVING
    if (this.havingConditions.length > 0) {
      sql += ` HAVING ${this.buildWhereConditionString(this.havingConditions)}`;
    }
    
    // 添加ORDER BY
    if (this.orderByConditions.length > 0) {
      const orderStr = this.orderByConditions.map(order => 
        `${order.field} ${order.direction}`
      ).join(', ');
      sql += ` ORDER BY ${orderStr}`;
    }
    
    // 添加LIMIT
    if (this.limitValue !== undefined) {
      sql += ` LIMIT ?`;
      
      // 添加OFFSET
      if (this.offsetValue !== undefined) {
        sql += ` OFFSET ?`;
      }
    }
    
    return {
      sql,
      params: this.params
    };
  }

  /**
   * 执行查询并返回所有结果
   */
  all<T = any>(): T[] {
    const { sql, params } = this.build();
    const stmt = this.db.prepare(sql);
    return stmt.all(...params) as T[];
  }

  /**
   * 执行查询并返回第一个结果
   */
  first<T = any>(): T | null {
    const currentLimit = this.limitValue;
    this.limitValue = 1;
    if (!this.params.includes(1)) {
      this.params.push(1);
    }
    
    const { sql, params } = this.build();
    const stmt = this.db.prepare(sql);
    const result = stmt.get(...params) as T;
    
    this.limitValue = currentLimit;
    return result || null;
  }

  /**
   * 执行查询并返回结果数量
   */
  count(): number {
    // 保存当前的查询字段
    const currentSelectFields = [...this.selectFields];
    this.selectFields = ['COUNT(*) as count'];
    
    // 移除ORDER BY，对COUNT没有影响
    const currentOrderBy = [...this.orderByConditions];
    this.orderByConditions = [];
    
    // 移除LIMIT和OFFSET
    const currentLimit = this.limitValue;
    const currentOffset = this.offsetValue;
    this.limitValue = undefined;
    this.offsetValue = undefined;
    
    // 调整参数
    const params = this.params.filter(p => 
      p !== currentLimit && p !== currentOffset
    );
    this.params = params;
    
    const { sql, params: queryParams } = this.build();
    const stmt = this.db.prepare(sql);
    const result = stmt.get(...queryParams) as { count: number } | undefined;
    
    // 恢复原始设置
    this.selectFields = currentSelectFields;
    this.orderByConditions = currentOrderBy;
    this.limitValue = currentLimit;
    this.offsetValue = currentOffset;
    
    return result?.count || 0;
  }
} 